/***************************************************************
PROGRAM: CaliforniaClean.sas
PURPOSE: Run Sarah's code to clean CA inpatient data files 
		 for PD hospital paper (Kaestner, Meyer, Miller, Wherry)
		 My changes are marked w/LW 
DATE:	 June 10, 2014
CONTACT: Laura Wherry, wherry@umich.edu
****************************************************************/

libname cadata '';
libname sidwork '';


PROC IMPORT OUT= WORK.camedincome 
            DATAFILE= "MedianIncome20072011ACS.csv" 
            DBMS=CSV REPLACE;
     GETNAMES=YES;
     DATAROW=2; 
RUN;


data camedincome (drop=GEO_id GEO_display_label Estimate MoE);
set camedincome;
patzip2=zipcode;
MedianIncome=Estimate;
if patzip2=. then delete;
run;

proc sort data=camedincome;
by patzip2;
run;

PROC IMPORT OUT= WORK.CCI
            DATAFILE= "E:\CAHosp_Analysis\cci2012.csv"
            DBMS=CSV REPLACE;
     GETNAMES=YES;
     DATAROW=2;
RUN;

data cci;
set cci;
DX1=ICD_9_CM_CODE;
run;

proc sort data=cci;
by DX1;
run;


%macro sid(year=);
data sidwork.temp (rename=(diag_p=dx1 odiag1=dx2 
odiag2=dx3 odiag3=dx4 odiag4=dx5 odiag5=dx6 odiag6=dx7 odiag7=dx8
odiag8=dx9 odiag9=dx10 odiag10=dx11 odiag11=dx12 odiag12=dx13
odiag13=dx14 odiag14=dx15 bthdate=bdate));
set cadata.wherry_pdd&year (rename=(race=oldrace));
FIPS=6;
Year=&year;
key=_N_;
hospid=.;
bmonth=month(bthdate);
byear=year(bthdate);
race=input(oldrace,7.);

*Define payers;
if pay_cat LT 3 then public=1;
else public=0;
if pay_cat=6 then public=1;
if pay_cat=3 then private=1;
else private=0;
if pay_cat=8 then self=1;
else self=0;
if pay_cat=5 then freecare=1;
else freecare=0;
if pay_cat=7 then freecare=1;

count=1;
ThreeDigDx2=substr(trim(left(dx1)),1,1);
if ThreeDigDx2="E" then ecode=1;
else ecode=0;
if ThreeDigDx2="V" then ThreeDigDx=substr(DX2,1,3)*1;
else ThreeDigDx=substr(DX1,1,3)*1; 
if ThreeDigDx LE 679 and ThreeDigDx GE 630 then preg=1;
else preg=0;
if threeDigDx LE 319 and ThreeDigDx GE 290 then mental=1;
else mental=0;
if threeDigDX GE 800 and threeDigDX LT 830 then fracture=1;
else fracture=0;
if threeDigDX GE 800 and threeDigDX LT 960 then injury=1;
else injury=0;
if threeDigDX=. then fracture=.;
if threeDigDX=. then mental=.;
if threeDigDX=. then injury=.;

if threeDigDX GE 540 and threeDigDX LT 544 then Appendicitis=1;
else Appendicitis=0;

if threedigdx GT 0 and threedigdx LE 139 then infect=1;
else infect=0;
if threedigdx GT 139 and threedigdx LE 239 then neoplasms=1;
else neoplasms=0;
if threedigdx GT 239 and threedigdx LE 279 then endometa=1;
else endometa=0;
if threedigdx GT 279 and threedigdx LE 289 then blood=1;
else blood=0;
if threedigdx GT 289 and threedigdx LE 319 then mentaldisorders=1;
else mentaldisorders=0;
if threedigdx GT 319 and threedigdx LE 359 then nervoussys=1;
else nervoussys=0;
if threedigdx GT 359 and threedigdx LE 389 then sense=1;
else sense=0;
if threedigdx GT 389 and threedigdx LE 459 then circulatory=1;
else circulatory=0;
if threedigdx GT 459 and threedigdx LE 519 then respiratory=1;
else respiratory=0;
if threedigdx GT 519 and threedigdx LE 579 then digestive=1;
else digestive=0;
if threedigdx GT 579 and threedigdx LE 629 then genitourinary=1;
else genitourinary=0;
if threedigdx GT 629 and threedigdx LE 679 then pregchild=1;
else pregchild=0;
if threedigdx GT 679 and threedigdx LE 709 then skin=1;
else skin=0;
if threedigdx GT 709 and threedigdx LE 739 then muscle=1;
else muscle=0;
if threedigdx GT 739 and threedigdx LE 759 then congenital=1;
else congenital=0;
if threedigdx GT 759 and threedigdx LE 779 then perinatal=1;
else perinatal=0;
if threedigdx GT 779 and threedigdx LE 799 then illdefined=1;
else illdefined=0;
if threedigdx GT 799 and threedigdx LE 999 then injpois=1;
else injpois=0;

if preg=1 then delete;
run;

**Chronic illness indicator;

proc sort data=sidwork.ca_sid_&year._core;
by DX1;
run;

data sidwork.ca_sid_&year._core;
merge sidwork.ca_sid_&year._core cci;
by DX1;
if count=. then delete;
Chronic=CATEGORY_DESCRIPTION*1;
run; 

proc sort data=sidwork.ca_sid_&year._core;
by ThreeDigDx;
quit;

***Imputed charges from HCUP data;

data hcup.charges;
set hcup.charges;
if ThreeDigDx=. then delete;
run;

proc sort data=hcup.charges;
by ThreeDigDx;
quit;

data sidwork.ca_sid_&year._core;
merge sidwork.ca_sid_&year._core hcup.charges;
by ThreeDigDx;
run;

%mend sid;


%sid(year=1999);

%sid(year=2009)

data sidwork.ca_sid_2009_core;
set sidwork.ca_sid_2009_core;
patzip2=patzip*1;
run;

proc sort data=camedincome;
by patzip2;
run;

proc sort data=sidwork.ca_sid_2009_core;
by patzip2;
run;

data sidwork.ca_sid_2009_core ;
merge sidwork.ca_sid_2009_core camedincome;
by patzip2;
if MedianIncome LE 39999 then zipinc_qrtl=1;
else zipinc_qrtl=2;
if MedianIncome = . then zipinc_qrtl=.;
if count=. then delete;
run;

***Sum visit counts;

proc sql;
create table California2009 as select 
sum(Chronic) as HCUPChronic,
sum(public*cost) as publicc,
sum(private*cost) as privatec,
sum(self*cost) as selfc,
sum(freecare*cost) as freecarec,

sum(TOTCHG) as charges,
sum(cost) as cost,

sum(infect) as infect,
sum(neoplasms) as neoplasms,
sum(endometa) as endometa,
sum(blood) as blood,
sum(mentaldisorders) as mentaldisorders,
sum(nervoussys) as nervoussys,
sum(sense) as sense,
sum(circulatory) as circulatory,
sum(respiratory) as respiratory,
sum(digestive) as digestive,
sum(genitourinary) as genitourinary,
sum(skin) as skin,
sum(muscle) as muscle,
sum(congenital) as congenital,
sum(perinatal) as perinatal,
sum(illdefined) as illdefined,
sum(injpois) as injpois,
sum(ecode) as ecode,
sum(count) as count,
sum(mental) as mental,
sum(injury) as injury,
sum(fracture) as fracture,
sum(preg) as preg,
sum(Appendicitis) as Appendicitis,
bmonth as bmonth,
byear as byear
from sidwork.ca_sid_2009_core
group by bmonth, byear;
quit;


data California2009;
set California2009;
if preg=. then preg=0;
if count=. then count=0;
FIPS=6;
run;


proc sql;
create table CaliforniaBlack2009 as select 
sum(Chronic) as HCUPChronic,
sum(public*cost) as publicc,
sum(private*cost) as privatec,
sum(self*cost) as selfc,
sum(freecare*cost) as freecarec,

sum(TOTCHG) as charges,
sum(cost) as cost,

sum(infect) as infect,
sum(neoplasms) as neoplasms,
sum(endometa) as endometa,
sum(blood) as blood,
sum(mentaldisorders) as mentaldisorders,
sum(nervoussys) as nervoussys,
sum(sense) as sense,
sum(circulatory) as circulatory,
sum(respiratory) as respiratory,
sum(digestive) as digestive,
sum(genitourinary) as genitourinary,
sum(skin) as skin,
sum(muscle) as muscle,
sum(congenital) as congenital,
sum(perinatal) as perinatal,
sum(illdefined) as illdefined,
sum(injpois) as injpois,
sum(ecode) as ecode,
sum(count) as count,
sum(mental) as mental,
sum(injury) as injury,
sum(fracture) as fracture,
sum(preg) as preg,
sum(Appendicitis) as Appendicitis,
bmonth as bmonth,
byear as byear
from sidwork.ca_sid_2009_core
where race=2
group by bmonth, byear;
quit;


data CaliforniaBlack2009;
set CaliforniaBlack2009;
if preg=. then preg=0;
if count=. then count=0;
FIPS=6;
run;

proc sql;
create table CalifornianotBlack2009 as select 
sum(Chronic) as HCUPChronic,
sum(public*cost) as publicc,
sum(private*cost) as privatec,
sum(self*cost) as selfc,
sum(freecare*cost) as freecarec,

sum(TOTCHG) as charges,
sum(cost) as cost,

sum(infect) as infect,
sum(neoplasms) as neoplasms,
sum(endometa) as endometa,
sum(blood) as blood,
sum(mentaldisorders) as mentaldisorders,
sum(nervoussys) as nervoussys,
sum(sense) as sense,
sum(circulatory) as circulatory,
sum(respiratory) as respiratory,
sum(digestive) as digestive,
sum(genitourinary) as genitourinary,
sum(skin) as skin,
sum(muscle) as muscle,
sum(congenital) as congenital,
sum(perinatal) as perinatal,
sum(illdefined) as illdefined,
sum(injpois) as injpois,
sum(ecode) as ecode,
sum(count) as count,
sum(mental) as mental,
sum(injury) as injury,
sum(fracture) as fracture,
sum(preg) as preg,
sum(Appendicitis) as Appendicitis,
bmonth as bmonth,
byear as byear
from sidwork.ca_sid_2009_core
where race NE 2  and race NE 6
group by bmonth, byear;
quit;


data CalifornianotBlack2009;
set CalifornianotBlack2009;
if preg=. then preg=0;
if count=. then count=0;
FIPS=6;
run;

**************Only low income zipcodes;

proc sql;
create table CaliforniaLI2009 as select 
sum(Chronic) as HCUPChronic,
sum(public*cost) as publicc,
sum(private*cost) as privatec,
sum(self*cost) as selfc,
sum(freecare*cost) as freecarec,

sum(TOTCHG) as charges,
sum(cost) as cost,

sum(infect) as infect,
sum(neoplasms) as neoplasms,
sum(endometa) as endometa,
sum(blood) as blood,
sum(mentaldisorders) as mentaldisorders,
sum(nervoussys) as nervoussys,
sum(sense) as sense,
sum(circulatory) as circulatory,
sum(respiratory) as respiratory,
sum(digestive) as digestive,
sum(genitourinary) as genitourinary,
sum(skin) as skin,
sum(muscle) as muscle,
sum(congenital) as congenital,
sum(perinatal) as perinatal,
sum(illdefined) as illdefined,
sum(injpois) as injpois,
sum(ecode) as ecode,
sum(count) as count,
sum(mental) as mental,
sum(injury) as injury,
sum(fracture) as fracture,
sum(preg) as preg,
sum(Appendicitis) as Appendicitis,
bmonth as bmonth,
byear as byear
from sidwork.ca_sid_2009_core
where ZIPINC_QRTL LT 2 
group by bmonth, byear;
quit;


data CaliforniaLI2009;
set CaliforniaLI2009;
if preg=. then preg=0;
if count=. then count=0;
FIPS=6;
run;



proc sql;
create table CaliforniaLIBlack2009 as select 
sum(Chronic) as HCUPChronic,
sum(public*cost) as publicc,
sum(private*cost) as privatec,
sum(self*cost) as selfc,
sum(freecare*cost) as freecarec,

sum(TOTCHG) as charges,
sum(cost) as cost,

sum(infect) as infect,
sum(neoplasms) as neoplasms,
sum(endometa) as endometa,
sum(blood) as blood,
sum(mentaldisorders) as mentaldisorders,
sum(nervoussys) as nervoussys,
sum(sense) as sense,
sum(circulatory) as circulatory,
sum(respiratory) as respiratory,
sum(digestive) as digestive,
sum(genitourinary) as genitourinary,
sum(skin) as skin,
sum(muscle) as muscle,
sum(congenital) as congenital,
sum(perinatal) as perinatal,
sum(illdefined) as illdefined,
sum(injpois) as injpois,
sum(ecode) as ecode,
sum(count) as count,
sum(mental) as mental,
sum(injury) as injury,
sum(fracture) as fracture,
sum(preg) as preg,
sum(Appendicitis) as Appendicitis,
bmonth as bmonth,
byear as byear
from sidwork.ca_sid_2009_core
where race=2 and ZIPINC_QRTL LT 2
group by bmonth, byear;
quit;

proc sql;
create table CaliforniaLIBlack2009 as select unique * from CaliforniaLIBlack2009;
quit;

data CaliforniaLIBlack2009;
set CaliforniaLIBlack2009;
if preg=. then preg=0;
if count=. then count=0;
FIPS=6;
run;


proc sql;
create table CaliforniaLInotBlack2009 as select 
sum(Chronic) as HCUPChronic,
sum(public*cost) as publicc,
sum(private*cost) as privatec,
sum(self*cost) as selfc,
sum(freecare*cost) as freecarec,

sum(TOTCHG) as charges,
sum(cost) as cost,

sum(infect) as infect,
sum(neoplasms) as neoplasms,
sum(endometa) as endometa,
sum(blood) as blood,
sum(mentaldisorders) as mentaldisorders,
sum(nervoussys) as nervoussys,
sum(sense) as sense,
sum(circulatory) as circulatory,
sum(respiratory) as respiratory,
sum(digestive) as digestive,
sum(genitourinary) as genitourinary,
sum(skin) as skin,
sum(muscle) as muscle,
sum(congenital) as congenital,
sum(perinatal) as perinatal,
sum(illdefined) as illdefined,
sum(injpois) as injpois,
sum(ecode) as ecode,
sum(count) as count,
sum(mental) as mental,
sum(injury) as injury,
sum(fracture) as fracture,
sum(preg) as preg,
sum(Appendicitis) as Appendicitis,
bmonth as bmonth,
byear as byear
from sidwork.ca_sid_2009_core
where race NE 2 and race NE 6 and ZIPINC_QRTL LT 2
group by bmonth, byear;
quit;


data CaliforniaLInotBlack2009;
set CaliforniaLInotBlack2009;
if preg=. then preg=0;
if count=. then count=0;
FIPS=6;
run;

***1999 visits;

proc sql;
create table California1999 as select 
sum(Chronic) as HCUPChronic,
sum(ecode) as ecode,
sum(count) as count,
sum(mental) as mental,
sum(injury) as injury,
sum(fracture) as fracture,
sum(preg) as preg,
sum(Appendicitis) as Appendicitis,
bmonth as bmonth,
byear as byear
from sidwork.ca_sid_1999_core
group by bmonth, byear;
quit;


data California1999;
set California1999;
if preg=. then preg=0;
if count=. then count=0;
FIPS=6;
run;


proc sql;
create table CaliforniaBlack1999 as select 
sum(Chronic) as HCUPChronic,
sum(ecode) as ecode,

sum(count) as count,
sum(mental) as mental,
sum(injury) as injury,
sum(fracture) as fracture,
sum(Appendicitis) as Appendicitis,
sum(preg) as preg,
bmonth as bmonth,
byear as byear
from sidwork.ca_sid_1999_core
where race=2
group by bmonth, byear;
quit;


data CaliforniaBlack1999;
set CaliforniaBlack1999;
if preg=. then preg=0;
if count=. then count=0;
FIPS=6;
run;



proc sql;
create table CalifornianotBlack1999 as select 

sum(Chronic) as HCUPChronic,
sum(ecode) as ecode,

sum(count) as count,
sum(mental) as mental,
sum(injury) as injury,
sum(fracture) as fracture,
sum(Appendicitis) as Appendicitis,
sum(preg) as preg,
bmonth as bmonth,
byear as byear
from sidwork.ca_sid_1999_core
where race NE 2 and race NE 6
group by bmonth, byear;
quit;


data CalifornianotBlack1999;
set CalifornianotBlack1999;
if preg=. then preg=0;
if count=. then count=0;
FIPS=6;
run;
